（术）MySQL 最佳实践

序言

  在 MySQL 的日常运用中，我们可能会遇到一些问题，针对这些问题，我们可以根据具体的业务来分析，取选择不同的解决方案。

逻辑删除与索引冲突

  在日常开发的数据库表设计中，我们经常会为表预留一个删除标记字段，方便对业务数据做逻辑删除，而不是物理删除。

  大多数时候这么做没有什么问题，但是，如果遇到表中使用了唯一索引字段，当我们将表中的数据进行逻辑删除后（设置标记为删除状态），若再往表中插入与之前删除数据唯一索引字段相同的数据时，会发现爆Duplicate entry错误，即不允许数据插入。

  在业务上，该值时必须要插入的。

  那么，遇到这种问题，应该如何去兼容呢？

  下面，我们就来聊聊针对这个问题的几种解决方案。

解决方案

方案一：物理删除

  不采用逻辑删除，直接物理删除。

方案二：新建历史表

  主表进行物理删除，但删除前将对应记录保存到一张额外的删除历史表中。

方案三：取消表的唯一约束，同时引入 Redis 来保证唯一约束

  取消表的唯一约束，在项目中引入redis，通过 redis 来判重，新增时往 redis set 记录，删除时，删除redis记录

方案四：变更删除标记为时间戳

  删除标记不使用0,1表示，而是以时间戳为值，默认取一个比较小的值（代表未删除），然后将删除状态为与之前的唯一约束 A 重新组成唯一联合约束index（A、del_flag），删除时变更del_flag的时间戳（代表已删除）。

方案五：保留删除标记，同时新建一个字段

  保留删除状态位，额外新增一个字段，这个字段如何创建，一般有两种实现思路：

• ① 新建del_unique_key字段，默认值为 0（代表未删除），字段类型和长度设置与主键id保持一致，新建完成后，将该字段与原先的唯一约束重新组成联合唯一约束index(A,del_unique_key)，然后删除原先的唯一索引，当业务进行逻辑删除，变更del_unique_key的值为该删除行的主键id即可（代表已删除）
• ② 新建delete_time字段，类型选择时间类型（也可用 int 类型代表时间戳），默认值用0000:00:00 00:00:00（代表未删除），新建完成后，将该字段与原先的唯一约束重新组成联合唯一约束index(A, delete_time)，然后删除原先的唯一索引，当业务进行逻辑删除，变更delete_time的值为当前时间即可（代表已删除）

  应该使用哪种呢？
  其实都可以，但 ② 可以记录删除时间而不需要额外字段，唯一的缺点是，在极端又的极端情况下，即联合唯一索引的数据删除后又立即新增又删除了，可以会有问题，这种非常极端情况基本不会发生。

方案选型

  方案一得看具体业务，如果物理删除，对业务没有影响，那么可以采用。

  方案二等于需要删除的记录的表都需要有历史表，如果仅仅是用来实现记录删除记录，大多数情况下感觉有点大材小用。

  方案三引入 Redis，虽然也可以解决问题，但是又额外增加复杂度，同时还得保证 Redis 和数据库的一致性。

  方案四和方案五其实实现的思路是一样，借助了版本号实现，那么，具体应当使用哪种呢？
  一般建议采用方案五。为什么呢？
  若遇到已经是在线上跑的业务，采用第五种方案更好，毕竟新增字段正常对已有的业务影响相对较小。此时若采用第四种方案，直接将标志位修改为时间戳，可能会影响到线上业务。

扩展——为什么数据库中的表经常冗余一个软删除字段呢？

  数据库中的表经常冗余一个删除标记字段（通常是一个布尔字段，如is_deleted或deleted，或者是一个时间戳字段，如deleted_at），这种做法通常是基于以下几个原因：

1. 性能考虑:
   • 直接从数据库中删除大量记录可能非常耗时并且对性能有较大影响，特别是在复杂的多表关联查询中。
   • 通过添加删除标记，实际的物理删除操作可以在系统负载较低时批量处理，从而减少直接影响。
2. 避免数据丢失:
   • 软删除提供了一种防止永久丢失数据的安全机制。
   • 如果数据被错误地删除，软删除可以让这些数据容易被找回。
3. 业务需要:
   • 某些业务逻辑需要记录数据的删除状态而不是实际上将其删除。
   • 例如，在一个社交网络应用中，用户可能希望删除一条消息，但系统还需要这条消息来显示用户的活动历史。
4. 简化删除操作:
   • 物理删除涉及的数据迁移和后期维护比更新一个字段更复杂。
   • 软删除只需简单地更新一个字段即可，操作简单并且容易实现。
5. 数据一致性:
   • 立即物理删除（Hard Delete）记录可能会导致与其他表的关联丢失，软删除则可以避免这种情况。
   • 如果有外键关联指向被删除的记录，直接删除可能会破坏数据的完整性或者需要大量的级联操作。

不过，使用软删除也有其缺点，例如可能会导致数据库中长期累积很多无用数据，这需要定期清理和维护。另外，在进行查询时需要额外注意过滤掉被标记为删除的数据，这可能会增加查询逻辑的复杂性。

总之，添加一个删除标记字段通常是为了提供更灵活的数据管理选项，并保留了更多的业务决策空间。在设计数据库和相关的业务逻辑时，开发者和架构师需要根据实际的业务需求和操作频率来决定是否使用软删除或硬删除策略。

覆盖更新处理

  事务并发时可能出现更新丢失 (覆盖) 的问题。

  发生更新丢失 (覆盖) 问题的关键在于多个事务执行”读取，计算，写回”的过程中，当前事务使用了过期的旧数据，而没有将其他并发事务对数据的修改纳入到计算结果中，从而在写回时将其他事务的更新覆盖了。

  那么，遇到这种情况应当如何解决？

解决方案

解决方案大致可以使用下面的几种：

① 防患未然

  既然出现了并发问题，我们能否让调用方将可能出现的并发调用改为串行调用，这样从源头上避免并发问题呢。

  如果客户好沟通的话，有时候还真可以！

② 悲观锁

  通过悲观锁的方式，在事务中对需要修改的结果集加行锁，常用的就是select * from table where id = ? for update加行锁，或者lock table对整个表加锁。加锁之后，在当前事务未处理完成读取的数据之前，其他所有需要访问锁定行的事务都必须等待。

  虽然能解决更新丢失 (覆盖) 的问题，但很明显会影响数据库的并发性能。

  如果使用了悲观锁，SQL 尽量带上 WHERE 条件，减少锁的数据范围。

③ 乐观锁

  与悲观锁相比，乐观锁不锁定任何行，而是通过版本号在更新数据时检查数据是否已经发生了变化，如果数据已经变化，则不更新。

  虽然使用乐观锁能提高并发性能，但是如果出现数据已经变化的情况，则不会更新本次数据，不会更新等于本次操作失败，因此需要引入额外的重试机制来保证操作执行成功。

④ 各行其道

  还有一种防止更新丢失 (覆盖) 的方法叫隔离更新（笔者自创的名词），大多数情况下开发可能是通过一些框架来执行 SQL，比如 Mybatis Plus，因此可能执行的是SELECT * FROM table WHERE id = ?这种语句来查询数据后，进行整行更新的，如果遇到多个事务并发处理数据，若表中有 A B C D E F 六个字段，可能甲事务只应操作 A B C 三个字段，而乙事务只应操作 D E F 三个字段，此时可以直接写 SQL 的方式分别更细粒度的控制 UPDATE 语句：

-- 甲事务
UPDATE table
SET A = ?, B = ?, C = ?
WHERE id = ?

-- 乙事务
UPDATE table
SET D = ?, E = ?, F = ?
WHERE id = ?

  但这不是一种通用的解决方案，只能根据业务逻辑来特定选择。

死锁处理 SQL

  在实际开发过程中，可能会遇到数据库死锁的问题，在此情况下可以通过下面的一些 SQL 来查看锁的一个相关情况：

-- 查看进程状态
SHOW FULL PROCESSLIST;  -- 定位 `State` 为 `Waiting for lock` 的会话

-- 查看当前锁等待
SELECT * FROM performance_schema.data_lock_waits;

-- 查看活跃事务
SELECT * FROM information_schema.innodb_trx;  -- 关注 `trx_state` 为 `LOCK WAIT` 的事务

-- 具体定位事务
SELECT 
    trx_id AS 事务ID,
    trx_mysql_thread_id AS 线程ID,
    trx_state AS 状态,
    trx_started AS 开始时间,
		TIMEDIFF(NOW(), trx_started) AS 持续时间,
    trx_query AS SQL内容
FROM information_schema.innodb_trx
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60  -- 筛选超过60秒的事务
--  AND trx_state = 'RUNNING'                         -- 定位具体状态
-- 	AND trx_query LIKE '%DELETE%'                     -- 定位具体 SQL 
	;

-- 获取kill列表
SELECT CONCAT('KILL ', trx_mysql_thread_id, ';') AS kill_command
FROM information_schema.innodb_trx
WHERE 
    trx_state = 'RUNNING'
		AND trx_query LIKE '%DELETE%'
    AND TIMESTAMPDIFF(MINUTE, trx_started, NOW()) > 10; -- 筛选超过10秒的事务

时间类型的思量

DATETIME VS TIMESTAMP

  两者均为时间类型字段，格式都一致，主要有以下四点区别：

• 时区影响不同（若应用场景有跨时区要求需特别注意这点）：
  • datetime 保存的是绝对值，没有时区，不会变化（若知道存入时的时区，取出时转换可解决跨时区问题，否则会丢失时区信息）
  • timestamp 会跟随设置的时区变化而变化（因为其内部存储的时间戳是不会变的，只是显示随时区变化而已，即其值存储时从当前时区转换为 UTC 存储，检索时从 UTC 转换回当前时区）
• 时间范围表示的不同：
  • timestamp 时间范围较小：1970-01-01 00:00:00 ~ 2038-01-09 03:14:07
  • datetime 支持的时间范围更广 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
• 存储空间占用不同：timestamp 储存占用 4 个字节，datetime 储存占用 8 个字节
• 索引速度不同：timestamp 更轻量，索引比 datetime 更快（其实速度上差别不是很大）

如何选择时间类型？

  基于前面的分析：

• 考虑存储空间的情况下，timestamp 是最优的时间存储类型
• 不考虑存储空间的情况下，datetime 是最优的时间存储类型
• 当然，还要看公司有没有国际化的业务场景，有的话需要注意时区问题

为什么有人时间类型选择 unsigned int ，而不是 timestamp?

  长久以来，MySQL 数据库里的时间戳都是用的 int(10) unsigned 类型，后来看了些别人的代码，发现用 timestamp 好像也蛮不错的。
  MySQL的 timestamp 类型内部其实本质是用的一个 int 整数来存储的从自 UTC 1970 年 1 月 1 日至今的秒数，所以无论是索引性能还是存储空间都没什么区别，MySQL 文档也有说明，当使用select from _unixtime(timestamp)时，是直接访问的内部秒数，而不是格式化后再反格式化。

  尽管大体一样，而 timestamp 类型带来了两点明显的好处：

• SELECT出来的结果直接就是可理解的
• 时间字段可以使用CURRENT_TIMESTAMP函数来设置默认值
• MySQL 及其他系统管理器提供了许多处理日期时间信息的函数，这些函数比自定义函数计算更快

  对于js代码中，new Date(timestamp * 1000)这种乘以1000的写法，总是让人遗忘，而数据库返回的结果直接就是格式化好的，js就只需要做截断或者字符替换操作，不容易出错得多。

  于是我将系统全更换成 timestamp 类型了，然而好景不长，我又全折腾回int unsigned了，因为面对它所带来的两大坏处，它带来的两点好处显得微不足道了：

• 可能的性能问题
• 绕不过去的时区问题

可能的性能问题

  默认情况下 MySQL 的时区变量取值为 SYSTEM，即跟随服务器系统的时区：

select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM      |
+-------------+

  这意味着每次SELECT的数据，都需要查询一下系统的时区设置，然后进行格式化，据说查询的过程还要获取全局锁，这就不仅有 CPU 的计算耗时问题，还会有阻塞问题。有建议的做法是设置@time_zone='+8'来避免频繁对系统时区的查询，但很显然，在布署新环境时，有可能会忘记这件事。

时区问题

  时区问题，我想了好久，也无法绕过去。
  中国时区是 CST，也就是 UTC+8（GMT+8），若应用只针对中国用户，那使用 CST 时区没有问题，但是若跨了时区，就必须存储 UTC 时间，然后各个客户端拿到这一绝对时间秒数后再转换成当地时区所对应的时间，特别像是美国及很多国家，不仅有时区问题，还有夏令时问题，这就更要依靠客户端的时区设置了。

  其实 timestamp 内部的 int 也是使用的 UTC 绝对秒数，但是在SELECT的时候，会转换成服务器所在时区的时间，这时问题就麻烦了，客户端拿到时间之后，正确的做法是先按服务器所在时区反格式化为绝对秒数，再根据当前时区设置格式化对应时间，这显然非常麻烦。

解决办法

  如果涉及到时区问题，最好的方案就是统一 UTC ，展示转换时区即可，即

• 不要使用 TIMESTAMP，统一用 DATETIME
• 如果要给 DATATIME 设置默认时间，用函数 UTC_TIMESTAMP() ，不要设置默认值 CURRENT_TIMESTAMP
• 程序里写入到 DATATIME 中的时间，统一为 UTC ，读取时统一按照 UTC 处理

扩展——GMT 和 UTC 有什么区别？

  可以认为 GMT 和 UTC 相等。

文章信息

时间	说明
2026-08-01	初稿